﻿#include <QAxObject>
#include <QFile>
#include <QStringList>
#ifdef QT_DEBUG
#include <QDebug>
#endif
#include "qexcel.h"
//add by wdg
QExcel::QExcel(QObject *parent)
{
    m_pSheet = 0;
    const QString strapp = "Excel.Application";
    m_pExcel = new QAxObject(strapp, parent);
    m_pWorkBooks = m_pExcel->querySubObject("Workbooks");

    m_pWorkBooks->dynamicCall("Add");
    m_pWorkBook = m_pExcel->querySubObject("ActiveWorkBook");
    m_pSheet = m_pWorkBook->querySubObject("Worksheets(int)", 1);
#if 0
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, m_pXlApp, L"Workbooks", 0);
        m_pXlBooks = result.pdispVal;
        //VariantClear(&result);
    }
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, m_pXlBooks, L"Add", 0);
        m_pXlBook = result.pdispVal;
        //VariantClear(&result);
    }
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, m_pXlBook, L"Sheets", 0);
        m_pXlSheets = result.pdispVal;
        //VariantClear(&result);
    }

    // Get ActiveSheet object
    //IDispatch *pXlSheet;
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, m_pXlApp, L"ActiveSheet", 0);
        m_pXlSheet = result.pdispVal;
        //VariantClear(&result);
    }

    // Get Range object for the Range A1:O15...
    //IDispatch *pXlRange;
    {
        VARIANT parm;
        parm.vt = VT_BSTR;
        parm.bstrVal = ::SysAllocString(L"A1:O15");

        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, m_pXlSheet, L"Range", 1, parm);
        VariantClear(&parm);

        m_pXlRange = result.pdispVal;
        VariantClear(&result);
    }
  //#if 0
    QAxObject* workbooks = excel->querySubObject("WorkBooks");
    workbooks->dynamicCall("Open (const QString&)", QString("d:\\projects\\QT\\ninja\\a.xls"));
    QAxObject* workbook = excel->querySubObject("ActiveWorkBook");
    QAxObject* worksheets = workbook->querySubObject("WorkSheets");

    QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", 1);
    QAxObject* usedrange = worksheet->querySubObject("UsedRange");
    QAxObject* rows = usedrange->querySubObject("Rows");
    QAxObject* columns = usedrange->querySubObject("Columns");
    int intRowStart = usedrange->property("Row").toInt();
    int intColStart = usedrange->property("Column").toInt();
    int intCols = columns->property("Count").toInt();
    int intRows = rows->property("Count").toInt();
    for (int i = intRowStart; i < intRowStart + intRows; i++)
    {
    for (int j = intColStart; j < intColStart + intCols; j++)
    {
    QAxObject * range = worksheet->querySubObject("Cells(int,int)", i, j );
    qDebug() << i << j << range->property("Value");
    }
    }
    excel->setProperty("DisplayAlerts", 0);
    workbook->dynamicCall("Save(void)");
    workbook->dynamicCall("Close (Boolean)", false);
    excel->setProperty("DisplayAlerts",1);


    delete excel;
#endif
}
void QExcel::showExcelApp(bool b)
{
    m_pExcel->setProperty("Visible", b);
}

//---add end------------------------------------------
QExcel::QExcel(QString xlsFilePath, QObject *parent)
{
    m_pSheet = 0;

    m_pExcel = new QAxObject("Excel.Application", parent);
    m_pWorkBooks = m_pExcel->querySubObject("Workbooks");
    QFile file(xlsFilePath);
    if (file.exists())
    {
        m_pWorkBooks->dynamicCall("Open(const QString&)", xlsFilePath);
        m_pWorkBook = m_pExcel->querySubObject("ActiveWorkBook");
        m_pSheets = m_pWorkBook->querySubObject("WorkSheets");
    }
}

QExcel::~QExcel()
{
   // close();
}

void QExcel::close()
{
    m_pExcel->dynamicCall("Quit()");

    delete m_pSheet;        m_pSheet = 0;
    delete m_pSheets;       m_pSheets = 0;
    delete m_pWorkBook;     m_pWorkBook = 0;
    delete m_pWorkBooks;    m_pWorkBooks = 0;
    delete m_pExcel;        m_pExcel = 0;
}

QAxObject *QExcel::getWorkBooks()
{
    return m_pWorkBooks;
}

QAxObject *QExcel::getWorkBook()
{
    return m_pWorkBook;
}

QAxObject *QExcel::getWorkSheets()
{
    return m_pSheets;
}

QAxObject *QExcel::getWorkSheet()
{
    return m_pSheet;
}

void QExcel::selectSheet(const QString& sheetName)
{
    m_pSheet = m_pSheets->querySubObject("Item(const QString&)", sheetName);
}

void QExcel::selectSheet(int sheetIndex)
{
    m_pSheet = m_pSheets->querySubObject("Item(int)", sheetIndex);
}

void QExcel::deleteSheet(const QString& sheetName)
{
    QAxObject * a = m_pSheets->querySubObject("Item(const QString&)", sheetName);
    a->dynamicCall("delete");
}

void QExcel::deleteSheet(int sheetIndex)
{
    QAxObject * a = m_pSheets->querySubObject("Item(int)", sheetIndex);
    a->dynamicCall("delete");
}

void QExcel::setCellString(int row, int column, const QString& value)
{
    QAxObject *range = m_pSheet->querySubObject("Cells(int,int)", row, column);
    range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontBold(const QString &cell, bool isBold)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Bold", isBold);
}

void QExcel::setCellFontBold(int row, int column, bool isBold)
{
    QString cell = RowColToCellStr(row, column);
    setCellFontBold(cell, isBold);
}

void QExcel::setCellFontSize(int row, int column, int size)
{
    QString cell = RowColToCellStr(row, column);

    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

void QExcel::mergeCells(const QString& cell)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn)
{
    QString cell = QString("%1:%2").arg(RowColToCellStr(topLeftRow, topLeftColumn)).arg(RowColToCellStr(bottomRightRow, bottomRightColumn));

    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

QVariant QExcel::getCellValue(int row, int column) const
{
    QAxObject *range = m_pSheet->querySubObject("Cells(int,int)", row, column);
    return range->property("Value");
}

void QExcel::save()
{
    m_pWorkBook->dynamicCall("Save()");
}

int QExcel::getSheetsCount()
{
    return m_pSheets->property("Count").toInt();
}

QString QExcel::getSheetName()
{
    return m_pSheet->property("Name").toString();
}

QString QExcel::getSheetName(int sheetIndex)
{
    QAxObject * a = m_pSheets->querySubObject("Item(int)", sheetIndex);
    return a->property("Name").toString();
}

void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn)
{
    QAxObject *usedRange = m_pSheet->querySubObject("UsedRange");
    *topLeftRow = usedRange->property("Row").toInt();
    *topLeftColumn = usedRange->property("Column").toInt();

    QAxObject *rows = usedRange->querySubObject("Rows");
    *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1;

    QAxObject *columns = usedRange->querySubObject("Columns");
    *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1;
}

void QExcel::setColumnWidth(int column, int width)
{
    QString columnName = ColToColStr(column);
    columnName = QString("%1:%2").arg(columnName).arg(columnName);

    QAxObject * col = m_pSheet->querySubObject("Columns(const QString&)", columnName);
    col->setProperty("ColumnWidth", width);
}

void QExcel::setColumnWidth(QString column, int width)
{
    column = column + ":" + column;

    QAxObject * col = m_pSheet->querySubObject("Columns(const QString&)", column);
    col->setProperty("ColumnWidth", width);
}

void QExcel::setCellTextCenter(int row, int column)
{
    QString cell = RowColToCellStr(row, column);

    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void QExcel::setCellTextWrap(int row, int column, bool isWrap)
{
    QString cell = RowColToCellStr(row, column);

    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void QExcel::setAutoFitRow(int row)
{
    QString rowsName = QString("%1:%2").arg(row).arg(row);

    QAxObject * rows = m_pSheet->querySubObject("Rows(const QString &)", rowsName);
    rows->dynamicCall("AutoFit()");
}

void QExcel::setAutoFitColumn(int column)
{
    QString columnName = ColToColStr(column);
    columnName = QString("%1:%2").arg(columnName).arg(columnName);
    
    QAxObject * col = m_pSheet->querySubObject("Columns(const QString&)", columnName);
    col->dynamicCall("AutoFit()");
}

void QExcel::insertSheet(QString sheetName)
{
    m_pSheets->querySubObject("Add()");
    QAxObject * a = m_pSheets->querySubObject("Item(int)", 1);
    a->setProperty("Name", sheetName);
}

void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow)
{
    int a,b,c,rowsCount;
    getUsedRange(&a, &b, &rowsCount, &c);

    int aMergeStart = topRow, aMergeEnd = topRow + 1;

    QString value;
    while(aMergeEnd <= rowsCount)
    {
        value = getCellValue(aMergeStart, column).toString();
        while(value == getCellValue(aMergeEnd, column).toString())
        {
            clearCell(aMergeEnd, column);
            aMergeEnd++;
        }
        aMergeEnd--;
        mergeCells(aMergeStart, column, aMergeEnd, column);

        aMergeStart = aMergeEnd + 1;
        aMergeEnd = aMergeStart + 1;
    }
}

void QExcel::clearCell(const QString& cell)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("ClearContents()");
}

void QExcel::clearCell(int row, int column)
{
    QString cell = RowColToCellStr(row, column);
    clearCell(cell);
}

int QExcel::getUsedRowsCount()
{
    QAxObject *usedRange = m_pSheet->querySubObject("UsedRange");
    int topRow = usedRange->property("Row").toInt();
    QAxObject *rows = usedRange->querySubObject("Rows");
    int bottomRow = topRow + rows->property("Count").toInt() - 1;
    return bottomRow;
}

void QExcel::setCellString(const QString& cell, const QString& value)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontSize(const QString &cell, int size)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

void QExcel::setCellTextCenter(const QString &cell)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void QExcel::setCellTextWrap(const QString &cell, bool isWrap)
{
    QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void QExcel::setRowHeight(int row, int height)
{
    QString rowsName = QString("%1:%2").arg(row).arg(row);
    QAxObject * r = m_pSheet->querySubObject("Rows(const QString &)", rowsName);
    r->setProperty("RowHeight", height);
}
//Donny added
QString QExcel::ColToColStr(int iCol)
{
    int C[3] = {0};
    int n = iCol;
    for(int i = 0; i < 3; i++)
    {
        int k = (n - 1) / 26;
        C[i] = n - 26 * k;
        if(k > 0)
        {
            n = k;
        }
        else
        {
            break;
        }
    }

    QString strCol("");
    for(int i = 2; i >= 0; i--)
    {
        if(C[i])
            strCol.append(C[i] + 'A' - 1);

    }
    return strCol;
}
QString QExcel::RowColToCellStr(int iRow, int iCol)
{//将 1,1 转成 "A1"
    QString strCol = ColToColStr(iCol);
    QString strRng = QString("%1%2").arg(strCol).arg(iRow);
    return strRng;
}
QAxObject * QExcel::getRange(const QString &strCell1, const QString &strCell2 /*=_T("")*/ )
{
    QString strRange = QString("%1:%2").arg(strCell1).arg( (""==strCell2) ? strCell1 : strCell2 );
    return m_pSheet->querySubObject("Range(const QString&)", strRange);
}
QAxObject * QExcel::getRange(int minRow, int minCol, int maxRow/*=0*/, int maxCol/*=0*/)
{
    if(0 == maxRow) maxRow = minRow;
    if(0 == maxCol) maxCol = minCol;
    QString strCell1 = RowColToCellStr(minRow, minCol);
    QString strCell2 = RowColToCellStr(maxRow, maxCol);
    QAxObject * pXlRange = getRange(strCell1, strCell2);
    return pXlRange;
}

void QExcel::setRangeFont(int minRow, int minCol, int maxRow, int maxCol, T_RangeFont * pFont)
{
    if(NULL == pFont) return;
    unsigned char ucMask = pFont->ucMask;
    if(0 == ucMask) return;

    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);
    range = range->querySubObject("Font");

    if(MASK_BIT_FONT_NAME & ucMask) //           (BYTE)0X01
    {
        range->setProperty("Name", pFont->strFontName);
    }
    if(MASK_BIT_FONT_SIZE & ucMask)//          (BYTE)0X02
    {
        range->setProperty("Size", pFont->ucFontSize);
    }
    if(MASK_BIT_FONT_BOLD & ucMask)//            (BYTE)0X04
    {
        range->setProperty("Bold", pFont->bBold);
    }
    if(MASK_BIT_FONT_COLOR & ucMask)//             (BYTE)0X08
    {
        range->setProperty("Color", pFont->dwFontColor);
    }
    if(MASK_BIT_FONT_ITALIC & ucMask)//           (BYTE)0X10
    {
        range->setProperty("Italic", pFont->bItalic);
    }
    if(MASK_BIT_FONT_UNDERLINE & ucMask)//        (BYTE)0X20
    {
        range->setProperty("Underline", pFont->bUnderline);
    }
    if(MASK_BIT_FONT_SHADOW & ucMask)//           (BYTE)0X40
    {
        range->setProperty("Shadow", pFont->bShadow);
    }
    if(MASK_BIT_FONT_STRIKETHROUGH & ucMask)//    (BYTE)0X80
    {
        range->setProperty("Strikethrough", pFont->bStrikeThrough);
    }
}
void QExcel::setRangeWrap(int minRow, int minCol, int maxRow, int maxCol, bool bWrap/*=true*/)
{
    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);
    range->setProperty("WrapText", bWrap);
}
QString QExcel::getCellText(int iRow, int iCol) const
{
    QVariant v = getCellValue(iRow, iCol);
    return v.toString();
}
void QExcel::setRangeAlignment(int minRow, int minCol, int maxRow, int maxCol, T_Alignment * pAlignment)
{
    if(NULL == pAlignment) return;
    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);
    range->setProperty("HorizontalAlignment", pAlignment->H);
    range->setProperty("VerticalAlignment", pAlignment->V);
}
void QExcel::setRangeBkColor(int minRow, int minCol, int maxRow, int maxCol, const QColor &colorRef)
{
    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);

    range = range->querySubObject("Interior");
    range->setProperty("Color", colorRef);
}
void QExcel::setRangeBorder(int minRow, int minCol, int maxRow, int maxCol, T_RangeBorder * pRangeBorder /* =NULL */)
{
    T_RangeBorder tRangeBorder;
    if(NULL == pRangeBorder)
    {
        pRangeBorder = &tRangeBorder;
        pRangeBorder->ucStyle = 1;
        pRangeBorder->ucWidth = 2;
        pRangeBorder->ucColorIndex = 1;
    }
    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);
    range->dynamicCall("BorderAround", pRangeBorder->ucStyle, pRangeBorder->ucWidth, pRangeBorder->ucColorIndex);
}
void QExcel::drawGrid(int iminRow, int iminCol, int imaxRow, int imaxCol, T_RangeBorder * pRangeBorder /*= NULL */)
{
    T_RangeBorder * pBorder = pRangeBorder;
    for(int iR = iminRow; iR <= imaxRow; iR++)
    {
        for(int iC = iminCol; iC <= imaxCol; iC++)
            setRangeBorder(iR, iC, iR, iC, pBorder);
    }
}
void QExcel::setRangeFormat(int minRow, int minCol, int maxRow, int maxCol, const QString strFmt)
{
    QAxObject *range = getRange(minRow, minCol, maxRow, maxCol);
    range->setProperty("NumberFormat", strFmt);
}
